/// <summary>
// CodeFx.MySqlProviders - .NET Providers for MySql using the framework CodeFx
// Laurent Morisseau
//
// This program is free software; you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
//
// Laurent Morisseau
/// </summary>
/// <author><a href="mailto:laurent.morisseau@gmail.com">Laurent Morisseau</a></author>


namespace CodeFx.MySqlProviders
{
    using System;
    using System.Text;
    using System.Web;
    using System.Data;
    using System.ComponentModel;
    using MySql.Data.MySqlClient;
    using System.Collections;
    using CodeFx;
using System.Web.UI.WebControls.WebParts;
    using CodeFx.Web;

    internal static partial class Aspnet_personalizationperuserDTO
    {
        internal static void Update_UserId_PathId(Aspnet_personalizationperuserDAL dataObject)
        {
            if (dataObject == null)
                return;

            MySqlConnection conn = ConnectionString.Current.OpenConnection; 
            MySqlCommand cmd = new MySqlCommand("UPDATE aspnet_personalizationperuser SET PageSettings = ?PageSettings, LastUpdatedDate = ?LastUpdatedDate WHERE UserId = ?UserId AND PathId = ?PathId", conn);
            dataObject.Write(cmd);
            try
            {
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        internal static bool Exist_UserId_PathId(uint UserId, uint PathId)
        {
            bool exist;
            string command = "select 1 from aspnet_personalizationperuser where UserId = ?UserId AND PathId = ?PathId";

            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
            MySqlCommand cmd = new MySqlCommand(command, conn);

            cmd.Parameters.Add(new MySqlParameter("?UserId", UserId));
            cmd.Parameters.Add(new MySqlParameter("?PathId", PathId));
            MySqlDataReader dr = null;
            try
            {
                dr = cmd.ExecuteReader();
                exist = dr.Read();
            }
            catch (Exception ex)
            {
                throw new DALException(cmd.CommandText, ex.InnerException);
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                conn.Close();
            }
            return exist;
        }


        internal static void DeleteUserState(uint ApplicationId, DateTime InactiveSinceDate, string UserName, string Path)
        {
            const string command="DELETE pu FROM aspnet_personalizationperuser pu inner join aspnet_users u on (pu.UserId = u.UserId) inner join aspnet_Paths p on (pu.PathId = p.PathId) where p.ApplicationId = ?ApplicationId AND (?InactiveSinceDate IS NULL OR u.LastActivityDate <= ?InactiveSinceDate) AND (?UserName IS NULL OR u.UserName = ?UserName) AND (?Path IS NULL OR p.Path = ?Path)";
            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
            MySqlCommand cmd = new MySqlCommand(command, conn);
            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            if (Path != null)
                cmd.Parameters.Add(new MySqlParameter("?Path", Path));
            else
                cmd.Parameters.Add(new MySqlParameter("?Path", MySqlDbType.VarChar));
            if (UserName != null)
                cmd.Parameters.Add(new MySqlParameter("?UserName", UserName));
            else
                cmd.Parameters.Add(new MySqlParameter("?UserName", MySqlDbType.VarChar));
            if (InactiveSinceDate != DateTime.MaxValue)
                cmd.Parameters.Add(new MySqlParameter("?InactiveSinceDate", InactiveSinceDate.ToUniversalTime()));
            else
                cmd.Parameters.Add(new MySqlParameter("?InactiveSinceDate", MySqlDbType.Datetime));
            try
            {
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }
        internal static void DeleteByFk_PathId_UserId(uint UserId, uint PathId)
        {

            string command = "delete s from aspnet_personalizationperuser s   where s.PathId =?PathId and s.UserId =?UserId";
            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
            MySqlCommand cmd = new MySqlCommand(command, conn);

            cmd.Parameters.Add(new MySqlParameter("?PathId", PathId));
            cmd.Parameters.Add(new MySqlParameter("?UserId", UserId));
            try
            {
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        internal static void Delete_AllState(uint ApplicationId)
        {
            const string command = "DELETE u FROM aspnet_personalizationperuser u inner join aspnet_Paths p on (p.PathId = u.PathId and p.ApplicationId=?ApplicationId)";
            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
            MySqlCommand cmd = new MySqlCommand(command, conn);

            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            try
            {
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        internal static Byte[] SelectByFk_UserId_PathId(uint UserId, uint PathId)
        {
            byte[] pagesettings = null;
            string command = "SELECT p.PageSettings FROM aspnet_personalizationperuser p WHERE p.PathId = ?PathId AND p.UserId = ?UserId";
            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
            MySqlCommand cmd = new MySqlCommand(command, conn);

            cmd.Parameters.Add(new MySqlParameter("?UserId", UserId));
            cmd.Parameters.Add(new MySqlParameter("?PathId", PathId));
            MySqlDataReader dr = null;
            try
            {
                dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
                if (dr.Read())
                {
                    pagesettings = dr.GetValue(0) as byte[];
                }
            }
            catch (Exception ex)
            {
                throw new DALException(cmd.CommandText, ex.InnerException);
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                conn.Close();
            }
            return pagesettings;
        
        }
        
        internal static object GetCountOfSharedState(bool AllUsersScope, uint ApplicationId, string Path, string UserName, DateTime InactiveSinceDate)
        {
            string command = string.Empty;
            if (AllUsersScope)
                command = "SELECT COUNT(*) FROM aspnet_personalizationallusers u inner join aspnet_Paths p on (u.PathId = p.PathId and p.ApplicationId = ?ApplicationId AND (?Path IS NULL OR p.Path LIKE ?Path))";
            else
                command = "SELECT COUNT(*) FROM aspnet_personalizationperuser pu inner join aspnet_users u on (pu.UserId = u.UserId AND (?UserName IS NULL OR u.UserName LIKE ?UserName) AND (?InactiveSinceDate IS NULL OR u.LastActivityDate <= ?InactiveSinceDate)) inner join aspnet_Paths p on (pu.PathId = p.PathId and p.ApplicationId = ?ApplicationId and ?Path IS NULL OR p.Path LIKE ?Path)";
            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
            MySqlCommand cmd = new MySqlCommand(command, conn);
            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId));
            if (Path != null)
                cmd.Parameters.Add(new MySqlParameter("?Path", Path));
            else
                cmd.Parameters.Add(new MySqlParameter("?Path", MySqlDbType.VarChar ));
            if (UserName != null)
                cmd.Parameters.Add(new MySqlParameter("?UserName", UserName));
            else
                cmd.Parameters.Add(new MySqlParameter("?UserName", MySqlDbType.VarChar));
            if (InactiveSinceDate != DateTime.MaxValue)
                cmd.Parameters.Add(new MySqlParameter("?InactiveSinceDate", InactiveSinceDate));
            else
                cmd.Parameters.Add(new MySqlParameter("?InactiveSinceDate", MySqlDbType.Datetime));
            try
            {
                return cmd.ExecuteScalar();
            }
            finally
            {
                conn.Close();
            }
        }


        internal static object CountSharedState(uint ApplicationId, string Path, string UserName, DateTime InactiveSinceDate)
        {
            MySqlConnection conn = ConnectionString.Current.OpenConnection; 
            MySqlCommand cmd = new MySqlCommand("SELECT count(*) FROM aspnet_personalizationperuser pu inner join aspnet_users u on (pu.UserId = u.UserId AND (?UserName IS NULL OR u.UserName LIKE ?UserName) AND (?InactiveSinceDate IS NULL OR u.LastActivityDate <= ?InactiveSinceDate)) inner join aspnet_Paths p on (pu.PathId = p.PathId and p.ApplicationId = ?ApplicationId AND (?Path IS NULL OR p.Path LIKE ?Path) ) ", conn);

            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId ));
            if (Path != null)
                cmd.Parameters.Add(new MySqlParameter("?Path", Path));
            else
                cmd.Parameters.Add(new MySqlParameter("?Path", MySqlDbType.VarChar ));
            if (UserName != null)
                cmd.Parameters.Add(new MySqlParameter("?UserName", UserName));
            else
                cmd.Parameters.Add(new MySqlParameter("?UserName", MySqlDbType.VarChar));
            if (InactiveSinceDate != DateTime.MaxValue)
                cmd.Parameters.Add(new MySqlParameter("?InactiveSinceDate", InactiveSinceDate));
            else
                cmd.Parameters.Add(new MySqlParameter("?InactiveSinceDate", MySqlDbType.Datetime));
            try
            {

                return cmd.ExecuteScalar();
            }
            finally
            {
                conn.Close();
            }
        }

        internal static PersonalizationStateInfoCollection FindSharedState(uint ApplicationId, string path, DateTime inactiveSinceDate, string username, int lbound, int ubound)
        {
            string command = "SELECT p.Path, pu.LastUpdatedDate, LENGTH(pu.PageSettings), u.UserName, u.LastActivityDate FROM aspnet_personalizationperuser pu inner join aspnet_users u on (pu.UserId = u.UserId AND (?UserName IS NULL OR u.UserName LIKE ?UserName) AND (?InactiveSinceDate IS NULL OR u.LastActivityDate <= ?InactiveSinceDate) ) inner join aspnet_Paths p on (pu.PathId = p.PathId and p.ApplicationId = ?ApplicationId AND (?Path IS NULL OR p.Path LIKE ?Path) ) LIMIT ?lbound, ?ubound";
            MySqlConnection conn = ConnectionString.Current.OpenConnection;       
            MySqlCommand cmd = new MySqlCommand(command, conn);
            cmd.Parameters.Add(new MySqlParameter("?ApplicationId", ApplicationId ));
            cmd.Parameters.Add(new MySqlParameter("?lbound", lbound));
            cmd.Parameters.Add(new MySqlParameter("?ubound", ubound));
            if (path != null)
                cmd.Parameters.Add(new MySqlParameter("?Path", path));
            else
                cmd.Parameters.Add(new MySqlParameter("?Path", MySqlDbType.VarChar ));
            if (username != null)
                cmd.Parameters.Add(new MySqlParameter("?UserName", username));
            else
                cmd.Parameters.Add(new MySqlParameter("?UserName", MySqlDbType.VarChar));
            if (inactiveSinceDate != DateTime.MaxValue)
                cmd.Parameters.Add(new MySqlParameter("?InactiveSinceDate", inactiveSinceDate));
            else
                cmd.Parameters.Add(new MySqlParameter("?InactiveSinceDate", MySqlDbType.Datetime ));
            PersonalizationStateInfoCollection al = null;
            MySqlDataReader dr = null;
            try
            {
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                    al = new PersonalizationStateInfoCollection();
                while (dr.Read())
                {
                    string text1 = dr.GetString(0);
                    DateTime time1 = dr.IsDBNull(1) ? DateTime.MinValue : DateTime.SpecifyKind(dr.GetDateTime(1), DateTimeKind.Utc);
                    int num1 = dr.IsDBNull(2) ? 0 : dr.GetInt32(2);
                    int num2 = dr.IsDBNull(3) ? 0 : dr.GetInt32(3);
                    int num3 = dr.IsDBNull(4) ? 0 : dr.GetInt32(4);
                    al.Add(new SharedPersonalizationStateInfo(text1, time1, num1, num2, num3));
                }
            }
            catch (Exception ex)
            {
                throw new DALException(cmd.CommandText, ex.InnerException);
            }
            finally
            {
                if (dr != null)
                    dr.Close();
                conn.Close();
            }
            return al;
        }
    }
}